# _*_ coding:utf-8 _*_
# @Time :2021/3/22 10:16
# @Author :yanxia
from email.header import Header
from urllib import parse
from application import db
from application.apps.quality_control.service.bug_calculation import Bug_Calculate as BC
from application.models.models import Collect, ReplyTest, TestReportTable
from application.models.mantis_models import MantisProjectVersionTable, MantisBugTable, MantisUserTable, \
    MantisCustomFieldStringTable
import time
from application.apps.quality_control import qdata_standard, const
from sqlalchemy import func, or_
import smtplib
import email.mime.text
import email.mime.multipart


def get_collect_data(pid):
    '''
    获取qa-p下collect的结果，返回这个pid的结果给其他函数调用
    '''
    result = db.session.query(Collect).filter(
        Collect.pid == pid).first()
    return result


def get_url_data(pid):
    '''
    生成mantis的url地址
    '''
    result = get_collect_data(pid)
    project_id = result.project_id
    version = result.version
    pversion = parse.quote(version)
    url = "https://domain/search.php?project_id=" + str(
        project_id) + "&sticky=on&version=" + pversion + "&sort=status%2Clast_updated&dir=ASC%2CDESC&hide_status=-2&match_type=0"
    return url


def get_user_experience(pid):
    '''
    获取用户体验的数
    '''
    result = get_collect_data(pid)
    project_id = result.project_id
    version = result.version
    user_experience = db.session.query(func.count(MantisBugTable.id)) \
        .filter(MantisBugTable.id == project_id, MantisBugTable.version == version,
                or_(MantisBugTable.severity == 10, MantisBugTable.category_id == 6)).first()
    return user_experience


def get_test_data(pid):
    '''
    回显提测邮件的数据
    '''
    res = db.session.query(ReplyTest).filter(ReplyTest.id == pid).first()
    if not res:
        result = get_collect_data(pid)
        group = result.group
        project = result.project
        version = result.version
        platform = ["PC", "M", "微信", "App"]
        url = get_url_data(pid)
        result_id = get_user_id(pid)
        rec_data = db.session.query(MantisUserTable.realname, MantisUserTable.email) \
            .filter(MantisUserTable.id.in_(result_id)).all()
        # 收件人列表
        cc_data = []
        user_data = {}
        for i in rec_data:
            # user_data["username"] = str(i.realname).split("-")[-1]
            # user_data["email"] = str(i.email)
            # cc_data.append(user_data.copy())
            cc_data.append(str(i.email))
        # cc = [{"email": "qa-group@360che.com", "username": "项目测试部"},
        #       {"email": "jianliang.liu@360che.com", "username": "刘建亮"},
        #       {"email": "kunyang.yu@360che.com", "username": "于昆洋"}, {"email": "yongfeng.ma@360che.com", "username": "麻永峰"},
        #       {"email": "zcy@360che.com", "username": "张春阳"}, {"email": "hailong.wang@360che.com", "username": "王海龙"},
        #       {"email": "fengrui@360che.com", "username": "冯睿"}]
        cc = ["qa-group@360che.com",
              "jianliang.liu@360che.com",
              "kunyang.yu@360che.com", "yongfeng.ma@360che.com",
              "zcy@360che.com", "hailong.wang@360che.com",
              "fengrui@360che.com", "shangxian-gp@360che.com", "app-gp@360che.com", "bigdata-gp@360che.com"]

        data = {
            "addressee": cc_data,
            "cc": cc,
            "project": group + "-" + project,
            "pversion": version,
            "platform": platform,
            "mantis_adress": url,
            "remarks": ""
        }
    else:
        data = {
            "addressee": eval(res.addressee),
            "cc": eval(res.cc),
            "project": res.project,
            "pversion": res.pversion,
            "platform": eval(res.platform),
            "mantis_adress": res.mantis_adress,
            "remarks": res.remarks
        }
    return data


def edit_testdata(data):
    """"
    修改提测邮件的数据
    """
    id = data["id"]
    addressee = data["addressee"]
    cc = data["cc"]
    project = data["project"]
    pversion = data["pversion"]
    platform = data["platform"]
    mantis_adress = data["mantis_adress"]
    remarks = data["remarks"]
    # addtime = time.strftime(
    #     "%Y-%m-%d %H:%M:%S",
    #     time.localtime())
    res = db.session.query(ReplyTest.id).filter(ReplyTest.id == id).all()
    if res:
        db.session.query(ReplyTest).filter(
            ReplyTest.id == id).update(
            {
                "addressee": str(addressee),
                "cc": str(cc),
                "project": project,
                "pversion": pversion,
                "platform": str(platform),
                "mantis_adress": mantis_adress,
                "remarks": remarks})
    else:
        add_testdata = ReplyTest()
        add_testdata.id = id
        add_testdata.addressee = str(addressee)
        add_testdata.cc = str(cc)
        add_testdata.project = project
        add_testdata.pversion = pversion
        add_testdata.platform = str(platform)
        add_testdata.mantis_adress = mantis_adress
        # add_testdata.addtime = addtime
        add_testdata.remarks = remarks
        db.session.add(add_testdata)
    # try:
    db.session.commit()
    msg = const.EDIT_SUCCESS
    # except:
    #     db.session.rollback()
    #     msg = const.EDIT_FAIL
    return msg


def get_report_data(pid):
    '''
    回显测试报告数据
    '''
    res = db.session.query(TestReportTable).filter(TestReportTable.id == pid).first()
    if not res:
        result = get_collect_data(pid)
        group = result.group
        project = result.project
        version = result.version
        start_data = result.begindate
        end_data = result.enddate
        total_bug = result.sumbugcount
        legacy_bug = result.yiliubug
        project_id = result.project_id
        bug_repair = int(total_bug) - int(legacy_bug)
        mantis_adress = get_url_data(pid)
        result_id = get_user_id(pid)
        rec_data = db.session.query(MantisUserTable.realname, MantisUserTable.email) \
            .filter(MantisUserTable.id.in_(result_id)).all()
        cc_data = []
        user_data = {}
        for i in rec_data:
            # user_data["username"] = str(i.realname).split("-")[-1]
            # user_data["email"] = str(i.email)
            cc_data.append(str(i.email))
        # cc = [{"email": "qa-group@360che.com", "username": "项目测试部"},
        #       {"email": "jianliang.liu@360che.com", "username": "刘建亮"},
        #       {"email": "kunyang.yu@360che.com", "username": "于昆洋"}, {"email": "yongfeng.ma@360che.com", "username": "麻永峰"},
        #       {"email": "zcy@360che.com", "username": "张春阳"}, {"email": "hailong.wang@360che.com", "username": "王海龙"},
        #       {"email": "fengrui@360che.com", "username": "冯睿"}]
        cc = ["qa-group@360che.com",
              "jianliang.liu@360che.com",
              "kunyang.yu@360che.com", "yongfeng.ma@360che.com",
              "zcy@360che.com", "hailong.wang@360che.com",
              "fengrui@360che.com", "shangxian-gp@360che.com", "app-gp@360che.com", "bigdata-gp@360che.com"]
        # data = BC.get_bugdata(group, project, version)
        bug_density = result.bugrate
        comments = db.session.query(func.count(MantisBugTable.id)).outerjoin(MantisCustomFieldStringTable,
                                                                             MantisBugTable.id == MantisCustomFieldStringTable.bug_id
                                                                             ).filter(
            MantisBugTable.project_id == project_id,
            MantisBugTable.version == version, MantisBugTable.category_id != 6,
            MantisBugTable.severity != 10,
            MantisBugTable.resolution.notin_([60, 70, 90]), MantisCustomFieldStringTable.value == "新出现").count()
        if result.sumbugcount and result.sumbugcount != 0:
            bug_introduction = round(comments / result.sumbugcount, 2)
        else:
            bug_introduction = 0
        user_experience = get_user_experience(pid)
        several_days = ""
        developer = ""
        qa = ""
        pro_manager = ""
        mantis_account = "guest\guest"
        remarks = '上线后请及时告知，以便线上走查。'
        project_name = group + "-" + project + "-" + version
        advice_counts = user_experience[0]
        conclusion = "测试通过"
    else:
        cc_data = eval(res.addressee)
        cc = eval(res.cc)
        project_name = res.project_name
        start_data = res.begin_time
        end_data = res.end_time
        several_days = res.several_days
        pro_manager = res.pro_manager
        developer = res.developer
        qa = res.qa
        total_bug = res.bug_counts
        bug_repair = res.bug_repair
        legacy_bug = res.bug_legacy
        advice_counts = res.advice_counts
        bug_density = res.bug_density
        bug_introduction = res.bug_introd
        conclusion = res.conclusion
        mantis_adress = res.mantis_adress
        mantis_account = res.mantis_account
        remarks = res.remarks
        conclusion = res.conclusion

    report_data = {
        "addressee": cc_data,
        "cc": cc,
        "project_name": project_name,
        "begin_time": start_data,
        "end_time": end_data,
        "several_days": several_days,
        "pro_manager": pro_manager,
        "developer": developer,
        "qa": qa,
        "bug_counts": total_bug,
        "bug_repair": bug_repair,
        "bug_legacy": legacy_bug,
        "advice_counts": advice_counts,
        "bug_density": bug_density,
        "bug_introd": bug_introduction,
        "conclusion": conclusion,
        "mantis_adress": mantis_adress,
        "mantis_account": mantis_account,
        "remarks": remarks
    }
    return report_data


def edit_report_data(data):
    '''
    修改测试报告数据
    '''
    id = data["id"]
    addressee = data["addressee"]
    cc = data["cc"]
    project_name = data["project_name"]
    begin_time = data["begin_time"]
    end_time = data["end_time"]
    several_days = data["several_days"]
    pro_manager = data["pro_manager"]
    developer = data["developer"]
    qa = data["qa"]
    bug_counts = data["bug_counts"]
    bug_repair = data["bug_repair"]
    bug_legacy = data["bug_legacy"]
    advice_counts = data["advice_counts"]
    bug_density = data["bug_density"]
    bug_introd = data["bug_introd"]
    conclusion = data["conclusion"]
    mantis_adress = data["mantis_adress"]
    mantis_account = data["mantis_account"]
    remarks = data["remarks"]
    addtime = time.strftime(
        "%Y-%m-%d %H:%M:%S",
        time.localtime())
    res = db.session.query(TestReportTable.id).filter(TestReportTable.id == id).all()
    if res:
        db.session.query(TestReportTable).filter(
            TestReportTable.id == id).update(
            {"addressee": str(addressee),
             "cc": str(cc),
             "project_name": project_name,
             "begin_time": begin_time,
             "end_time": end_time,
             "several_days": several_days,
             "pro_manager": pro_manager,
             "developer": developer,
             "qa": qa,
             "bug_counts": bug_counts,
             "bug_repair": bug_repair,
             "bug_legacy": bug_legacy,
             "advice_counts": advice_counts,
             "bug_density": bug_density,
             "bug_introd": bug_introd,
             "conclusion": conclusion,
             "mantis_adress": mantis_adress,
             "mantis_account": mantis_account,
             "remarks": remarks,
             "addtime": addtime})
    else:
        add_report_data = TestReportTable()
        add_report_data.id = id
        add_report_data.addressee = str(addressee)
        add_report_data.cc = str(cc)
        add_report_data.project_name = project_name
        add_report_data.begin_time = begin_time
        add_report_data.end_time = end_time
        add_report_data.several_days = several_days
        add_report_data.pro_manager = pro_manager
        add_report_data.developer = developer
        add_report_data.qa = qa
        add_report_data.bug_counts = bug_counts
        add_report_data.bug_repair = bug_repair
        add_report_data.bug_legacy = bug_legacy
        add_report_data.advice_counts = advice_counts
        add_report_data.bug_density = bug_density
        add_report_data.bug_introd = bug_introd
        add_report_data.conclusion = conclusion
        add_report_data.mantis_adress = mantis_adress
        add_report_data.mantis_account = mantis_account
        add_report_data.remarks = remarks
        add_report_data.addtime = addtime
        db.session.add(add_report_data)
    # try:
    db.session.commit()
    msg = const.EDIT_SUCCESS
    # except:
    #     db.session.rollback()
    #     msg = const.EDIT_FAIL
    return msg


def get_user_id(pid):
    '''
    获取mantis用户的id
    '''
    result = get_collect_data(pid)
    project_id = result.project_id
    version = result.version
    data = db.session.query(func.distinct(MantisBugTable.handler_id)) \
        .filter(MantisBugTable.project_id == project_id, MantisBugTable.version == version).all()
    cc_list = []
    for i in data:
        cc_list.append(i[0])
    return tuple(cc_list)


def check_test_emaildata(pid):
    '''
        获取发送提测邮件的数据
    '''
    result = db.session.query(ReplyTest.project, ReplyTest.pversion, ReplyTest.platform,
                              ReplyTest.mantis_adress, ReplyTest.remarks).filter(
        ReplyTest.id == pid).first()
    result_dic = {
        "email_theme": result[0] + "-" + result[1],
        "project": result[0],
        "pversion": result[1],
        "platform": result[2],
        "mantis_adress": result[3],
        "remarks": result[4]
    }
    return result_dic


def check_replytest_recipient(pid):
    '''
    获取回复提测邮件的收件人数据
    '''
    result = db.session.query(ReplyTest.addressee).filter(ReplyTest.id == pid).first()
    addressee = eval(result[0])
    # addressee = []
    # for i in data:
    #     addressee.append(i["email"])
    return addressee


def check_replytest_cc(pid):
    '''
    获取回复提测邮件抄送人数据
    '''
    result = db.session.query(ReplyTest.cc).filter(ReplyTest.id == pid).first()
    cc = eval(result[0])
    # cc = []
    # for i in data:
    #     cc.append(i["email"])
    return cc


def get_data_standand():
    '''
    获取回复测试报告质量标准数据
    '''
    standard_valve = qdata_standard.qulity_data()
    return standard_valve["bugmid"], standard_valve["introerror"]


def check_report_recipient(pid):
    '''
    获取回复测试报告的收件人数据
    '''
    result = db.session.query(TestReportTable.addressee).filter(TestReportTable.id == pid).first()
    data = eval(result[0])
    # addressee = []
    # for i in data:
    #     addressee.append(i["email"])
    return data


def check_report_cc(pid):
    '''
    获取回复测试报告邮件抄送人数据
    '''
    result = db.session.query(TestReportTable.cc).filter(TestReportTable.id == pid).first()
    data = eval(result[0])
    # cc = []
    # for i in data:
    #     cc.append(i["email"])
    return data


def check_report_emaildata(pid):
    '''
        获取发送提测邮件的右侧数据
    '''
    result = db.session.query(TestReportTable.project_name, TestReportTable.begin_time, TestReportTable.end_time,
                              TestReportTable.several_days, TestReportTable.pro_manager, TestReportTable.developer,
                              TestReportTable.qa, TestReportTable.bug_counts, TestReportTable.bug_repair,
                              TestReportTable.bug_legacy,
                              TestReportTable.advice_counts, TestReportTable.bug_density, TestReportTable.bug_introd,
                              TestReportTable.conclusion, TestReportTable.mantis_adress,
                              TestReportTable.remarks).filter(
        TestReportTable.id == pid).first()
    result_dic = {
        "project_name": result[0],
        "begin_time": result[1],
        "end_time": result[2],
        "several_days": result[3],
        "pro_manager": result[4],
        "developer": result[5],
        "qa": result[6],
        "bug_counts": result[7],
        "bug_repair": result[8],
        "bug_legacy": result[9],
        "advice_counts": result[10],
        "bug_density": result[11],
        "bug_introd": result[12],
        "conclusion": result[13],
        "mantis_adress": result[14],
        "remarks": result[15]
    }
    return result_dic


def send_email_html(receiver, cc, subject, content):
    """
    :param receiver: 收件人邮箱
    :param cc: 抄送人邮箱
    :param subject: 邮件标题
    :param content: 邮件内容
    :return:
    """
    email_name = 'qa@360che.com'
    email_passwd = '360CHEche'

    msg = email.mime.multipart.MIMEMultipart()
    msg['from'] = email_name
    msg['to'] = str(receiver)[1:-1].replace("'", "")
    msg['subject'] = subject
    msg['cc'] = str(cc)[1:-1].replace("'", "")
    txt = email.mime.text.MIMEText(content, 'html', 'utf-8')
    msg.attach(txt)
    smtp = smtplib.SMTP()
    smtp.connect('smtp.mxhichina.com', '25')
    smtp.login(email_name, email_passwd)
    smtp.sendmail(email_name, (receiver + cc), str(msg))
    smtp.quit()
